package com.dm.saleassback.assessmanage.service.impl;

import com.dm.saleassback.assessmanage.mapper.AssessMapper;
import com.dm.saleassback.assessmanage.service.AssessService;
import com.dm.saleassback.common.util.EncryptionUtil;
import net.sf.json.JSONArray;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class AssessServiceImpl implements AssessService {
    @Autowired
    private AssessMapper assessMapper;


    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        return DriverManager.getConnection("jdbc:sqlserver://192.168.1.135\\SQL2008;DatabaseName=DMHR", "binghe_2017_oa", "Binghe*5211314");
    }

    public String queryUserInfosByUserNo(Map<String, Object> param) throws SQLException {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet res = null;
        String username1 = param.get("SalerID")!=null?param.get("SalerID").toString():null;
        String username = "";
        String sql = "select a.yg_ming as Name,a.yg_mm as Password,b.ming as bm,c.gw as gw,b.shibie as Store   from ygb a \n" +
                "LEFT JOIN jigoudiguib b on a.yg_bumen = b.id \n" +
                "LEFT JOIN gwb c on a.yg_gangwei=c.id \n" +
                "where a.yg_zaizhi='在职' and a.yg_zaigang='在岗'  and a.yg_id = "+username1;
        try {
            conn = getConnection();
            stat = conn.prepareStatement(sql);
            res = stat.executeQuery();
            if(res.next()){
                username = res.getString("Name");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.closeConnection(conn);
            JdbcUtils.closeStatement(stat);
            JdbcUtils.closeResultSet(res);
        }
        return username;
    }

    /**
     * @Description 查询考核信息
     * @Author zhangxue
     * @Date 16:56 2018/5/31
     * @Param
     * @retrun
     **/
    @Override
    public Map<String, Object> queryAllAssess(Map<String, Object> param) {
        List<Map<String,Object>> resultList = new ArrayList<Map<String,Object>>();
        resultList = assessMapper.queryAllAssessMapper(param);

        //通过resultList的SalerID查询对应人员信息
        for(int i=0;i<resultList.size();i++){
            try {
                String SalerName = queryUserInfosByUserNo(resultList.get(i));
                resultList.get(i).put("SalerName",SalerName);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }


        Double totalFactMoney = 0.0;
        for(int i=0;i<resultList.size();i++){
            Double base = Double.valueOf(resultList.get(i).get("Base").toString());
            Double proportion = Double.valueOf(resultList.get(i).get("Proportion").toString());
            Double finishrate = Double.valueOf(resultList.get(i).get("FinishRate").toString());
            Double IsOrNotMoney = Double.valueOf(resultList.get(i).get("IsOrNotMoney").toString());
            Double factmoney = base*(finishrate/100)*(proportion/100)*IsOrNotMoney;
            factmoney = (double)Math.round(factmoney*100)/100;
            resultList.get(i).put("FactMoney",factmoney);
            resultList.get(i).put("RowID",i+1);
            totalFactMoney += factmoney;
        }
        //添加当前页合计footer
        Map<String,Object> tempMap = new HashMap<String,Object>();
        tempMap.put("Store","总计");
        tempMap.put("SalerName",totalFactMoney);
        resultList.add(tempMap);

//        //添加当前查询条件合计
//        List<Map<String,Object>> tempList = assessMapper.queryAllAssessByConditionMapper(param);
//        Double totalFactMoney2 = 0.0;
//        for(int i=0;i<tempList.size();i++){
//            Double base2 = Double.valueOf(tempList.get(i).get("Base").toString());
//            Double proportion2 = Double.valueOf(tempList.get(i).get("Proportion").toString());
//            Double finishrate2 = Double.valueOf(tempList.get(i).get("FinishRate").toString());
//            String tempIsOrNot = tempList.get(i).get("IsOrNotMoney")!=null?tempList.get(i).get("IsOrNotMoney").toString():null;
//            Double IsOrNotMoney = Double.valueOf(tempIsOrNot);
//            Double factmoney2 = base2*(finishrate2/100)*(proportion2/100)*IsOrNotMoney;
//            factmoney2 = (double)Math.round(factmoney2*100)/100;
//            totalFactMoney2 += factmoney2;
//        }
//        Map<String,Object> tempMap2 = new HashMap<String,Object>();
//        tempMap2.put("Store","总计");
//        tempMap2.put("SalerName",totalFactMoney2);
//        resultList.add(tempMap2);

        Map<String,String> countMap = assessMapper.queryAssessCountMapper(param);
        Object totalSize = countMap.get("total");
        Map<String,Object> resultMap = new HashMap<String, Object>();
        JSONArray ja = JSONArray.fromObject(resultList);
        resultMap.put("data",ja.toString());
        resultMap.put("totalnum",totalSize);
        return resultMap;
    }

    /**
     * @Description 新增考核信息
     * @Author zhangxue
     * @Date 18:13 2018/6/1
     * @Param
     * @retrun
     **/
    @Override
    public Map<String, Object> saveAssess(Map<String, Object> param) {
        List<Map<String,Object>> monthList = assessMapper.queryNowMonthAssessData(param);
        Map<String,Object> resultMap = new HashMap<String, Object>();
        if (monthList.size()>0){
            resultMap.put("status","2");//代表当月已经录入信息
        }else{
            int i = assessMapper.saveAssessMapper(param);
            if(i==1){
                resultMap.put("status","1");
            }else{
                resultMap.put("status","0");
            }
        }
        return resultMap;
    }

    /**
     * @Description 删除考核信息
     * @Author zhangxue
     * @Date 18:13 2018/6/1
     * @Param
     * @retrun
     **/
    @Override
    public Map<String, Object> deleteAssessById(Map<String, Object> param) {
        int i = assessMapper.deleteAssessById(param);
        Map<String,Object> resultMap = new HashMap<String, Object>();
        if(i==1){
            resultMap.put("status","1");
        }else{
            resultMap.put("status","0");
        }
        return resultMap;
    }

    /**
     * @Description 更新考核信息
     * @Author zhangxue
     * @Date 18:13 2018/6/1
     * @Param
     * @retrun
     **/
    @Override
    public Map<String, Object> updateAssessById(Map<String, Object> param) {
        int i = assessMapper.updateAssessByIdMapper(param);
        Map<String,Object> resultMap = new HashMap<String, Object>();
        if(i==1){
            resultMap.put("status","1");
        }else{
            resultMap.put("status","0");
        }
        return resultMap;
    }

    @Override
    public Map<String, Object> queryAccessCategory(Map<String, Object> param) {
        List<Map<String,Object>> resultList = new ArrayList<Map<String,Object>>();
        resultList = assessMapper.queryAccessCategoryMapper(param);
        List<Map<String,Object>> sameList = new ArrayList<Map<String, Object>>();
        for(int i=0;i<resultList.size();i++){
            Map<String,Object> tempMap = new HashMap<String, Object>();
            tempMap.put("CID",resultList.get(i).get("CID"));
            tempMap.put("CName",resultList.get(i).get("CName"));
            boolean flag = true;
            for(int j=0;j<sameList.size();j++){
                if(tempMap.get("CID")==sameList.get(j).get("CID")){
                    flag = false;
                }
            }
            if(flag){
                sameList.add(tempMap);
            }
        }
        Map<String,Object> resultMap = new HashMap<String, Object>();
        JSONArray ja = JSONArray.fromObject(sameList);
        resultMap.put("data",ja.toString());
        return resultMap;
    }

    @Override
    public Map<String, Object> queryAccessProject(Map<String, Object> param) {
        List<Map<String,Object>> resultList = new ArrayList<Map<String,Object>>();
        resultList = assessMapper.queryAccessProjectMapper(param);
        List<Map<String,Object>> sameList = new ArrayList<Map<String, Object>>();
        for(int i=0;i<resultList.size();i++){
            Map<String,Object> tempMap = new HashMap<String, Object>();
            tempMap.put("PID",resultList.get(i).get("PID"));
            tempMap.put("PName",resultList.get(i).get("PName"));
            boolean flag = true;
            for(int j=0;j<sameList.size();j++){
                if(tempMap.get("PID")==sameList.get(j).get("PID")){
                    flag = false;
                }
            }
            if(flag){
                sameList.add(tempMap);
            }
        }
        Map<String,Object> resultMap = new HashMap<String, Object>();
        JSONArray ja = JSONArray.fromObject(sameList);
        resultMap.put("data",ja.toString());
        return resultMap;
    }

    @Override
    public Map<String, Object> queryAccessStandard(Map<String, Object> param) {
        List<Map<String,Object>> resultList = new ArrayList<Map<String,Object>>();
        resultList = assessMapper.queryAccessStandardMapper(param);
//        List<Map<String,Object>> sameList = new ArrayList<Map<String, Object>>();
//        for(int i=0;i<resultList.size();i++){
//            Map<String,Object> tempMap = new HashMap<String, Object>();
//            tempMap.put("ID",resultList.get(i).get("ID"));
//            tempMap.put("Name",resultList.get(i).get("Name"));
//            boolean flag = true;
//            for(int j=0;j<sameList.size();j++){
//                if(tempMap.get("ID")==sameList.get(j).get("ID")){
//                    flag = false;
//                }
//            }
//            if(flag){
//                sameList.add(tempMap);
//            }
//        }
        Map<String,Object> resultMap = new HashMap<String, Object>();
        JSONArray js = JSONArray.fromObject(resultList);
        resultMap.put("data",js.toString());
        return resultMap;
    }

    /**
         * @Description 导出Excel
         * @Author zhangxue
         * @Date 15:01 2018/6/14
         * @Param
         * @retrun
         **/
    @Override
    public Map<String, Object> CreateAssessExcel(Map<String, Object> param) {
        List<Map<String,Object>> resultList = assessMapper.QueryExcelByConditionMapper(param);
        for(int i=0;i<resultList.size();i++){
            try {
                String SalerName = queryUserInfosByUserNo(resultList.get(i));
                resultList.get(i).put("SalerName",SalerName);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        Map<String,Object> resultMap = new HashMap<String, Object>();
        resultMap.put("data",resultList);
        return resultMap;
    }
}
